
package com.jk.utils;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
@SuppressWarnings("all")
public class Daoru {//导入
	
	public static List daoru(MultipartFile filename,String[] fieldarr,Class cc){
		//1、创建导入的文件
		InputStream input;
		List list = null;
		try {
			input = filename.getInputStream();
			//2、创建excel的工作表
			//获取上传的文件名称
			String oldname = filename.getOriginalFilename();
			//判断是03还是07
			Workbook book = null;
			if(oldname.endsWith(".xls")){//03   以...结尾
				book = new HSSFWorkbook(input);
			}else{//07
				book = new XSSFWorkbook(input);
			}	
			//3、获取sheet页
			Sheet sheet = book.getSheetAt(0);
			//4、获取表格的值
			//获取行数
			int rownum = sheet.getLastRowNum();//2
			
			JSONArray jsonArray = new JSONArray();
			for (int i = 0; i < rownum ; i++) {
				Row row = sheet.getRow(i+1);
				
				JSONObject jsonObject = new JSONObject();
				//{"id","name".....}
				for (int j = 0; j < fieldarr.length; j++) {
					//String value = row.getCell(j).getStringCellValue();
					Cell cell = row.getCell(j);
					if(cell!=null){
						//调用方法获取单元格的值
						String value = getMyCellType(cell);
						//把值存放josn对象
						jsonObject.put(fieldarr[j], value);
					}
				}
				jsonArray.add(jsonObject);
			}		
			//把json数组转换成list集合
			list = JSONArray.parseArray(jsonArray.toJSONString(), cc);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return list;
	}
    //获得cell的值
    private static String getMyCellType(Cell cell){
        String value = "";
        switch (cell.getCellType()) {//获取单元格的类型
            case Cell.CELL_TYPE_NUMERIC: // 数字或日期 0
                //如果为时间格式的内容
                if (DateUtil.isCellDateFormatted(cell)) {//日期
                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    value=sdf.format(cell.getDateCellValue()).toString();
                    break;
                } else {//数值
                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
                }
                break;
            case Cell.CELL_TYPE_STRING: // 字符串 1
                value = cell.getStringCellValue();
                break;
            case Cell.CELL_TYPE_BOOLEAN: // Boolean 4
                value = cell.getBooleanCellValue() + "";
                break;
            case Cell.CELL_TYPE_FORMULA: // 公式 2
                value = cell.getCellFormula() + "";
                break;
            case Cell.CELL_TYPE_BLANK: // 空值 3
                value = "";
                break;
            case Cell.CELL_TYPE_ERROR: // 故障 5
                value = "非法字符";
                break;
            default:
                value = "未知类型";
                break;
        }
        return value;
    }   
}
/*
	
--------------------------------------------------------------------------------
<div>
		<a onclick="javascript:daorutankuang()" class="easyui-linkbutton">导入弹框</a>
	</div>
  		<!-- 导入excel弹框 -->
	<div id="daorutankuang" class="easyui-dialog" data-options="title:'导入信息',closed:true,modal:true">
	<form id="daoruform" method="post" enctype="multipart/form-data">
  		<input class="easyui-filebox" name="filename" data-options="buttonText:'选择文件'" style="width:300px">
	</form>
  		<a onclick="javascript:daoru()" class="easyui-linkbutton">导入</a>
	</div>
--------------------------------------------------------------------------------
<script type="text/javascript">
function daorutankuang(){//打开导入弹框：上传excel
	$("#daorutankuang").dialog("open");
}
function daoru(){//导入：上传+导入
	$("#daoruform").form("submit",{
		url:"<%=path%>/yan/daoru.do",
		success:function(){
			$.messager.alert("提示","导入成功！","info");
			$("#daorutankuang").dialog("close");//关闭弹框
			$('#mytable').datagrid("load");//刷新表格
		}
	});
}
<script>
--------------------------------------------------------------------------------
	@RequestMapping("daoru")//导入
	@ResponseBody
	private void daoru(MultipartFile filename) {
		yanservice.daoru(filename);
	}
--------------------------------------------------------------------------------
service层

	public void daoru(MultipartFile filename) {//导入
		String[] fieldarr = {"name","name2","bsex","bfuxuan","bstatus","bdate"};
		//1、上传的文件 filename   2、导入的对象的属性名  3、类名称
		List<Biao1db> list = Daoru.daoru(filename, fieldarr , Biao1db.class);
		
		for (Biao1db aa : list) {
			//Integer类型数据导入时需在pojo封装层定义string格式业务字段接受判定
			if (aa.getBsex().equals("男")) {
				aa.setBsex(1);
			}if (aa.getBsex().equals("女")) {
				aa.setBsex(2);
			}if (aa.getBstatus().equals("上架")) {
				aa.setBstatus(1);
			}if (aa.getBstatus().equals("下架")) {
				aa.setBstatus(2);
			}
			mongoTemplate.save(aa);//mongodb数据库导入用
			yandao.daoru(aa);//oracle数据库导入用
		}	
	}
--------------------------------------------------------------------------------
	void daoru(guanshangpin aa);//oracle数据库导入
--------------------------------------------------------------------------------
	//正常新增语句
	<!-- 导入 -->
	<insert id="daoru">
		insert into guanshangpin values(aa.nextval,#{name},#{btype},'',#{byprice},#{bcount},#{btu},#{bstate})
	</insert>
--------------------------------------------------------------------------------

*/
